How to Add WITH (NOLOCK) and Handle Parameter Sniffing in Entity Framework

WARNING

The implementation method in this article may be considered an Anti-Pattern in some modern scenarios. For more recommended alternatives (such as the RCSI architecture and TagWith implementation), please refer to the latest comprehensive discussion: Introduction to RCSI and Improved Entity Framework Locking Hint Interceptor

I was asked about SQL Server's WITH (NOLOCK) during an interview a few days ago. Because I hadn't used it in a long time, I couldn't recall it immediately, which led to an incorrect answer. It turns out I had relevant content in my notes from a year ago: "SQL Server Performance Tuning".

Why is WITH (NOLOCK) important in SQL Server, yet I haven't used it in a long time? The main reason is that most development today uses Entity Framework directly, rather than manually writing libraries to generate SQL statements as in the past. It is much easier to let the library modify the SQL before executing the Command.

Now, let's look at how to achieve the same behavior in Entity Framework.

Interceptor

The Interceptor for Microsoft.EntityFrameworkCore was added in version 3.0, while it was added to the .NET Framework's EntityFramework in version 6.0. Its main function is to allow modification or interception of ongoing operations when Entity Framework executes low-level database operations or SaveChanges(). For more specific details, please refer to MSDN's "Interception". This article uses the Microsoft.EntityFrameworkCore version as an example.

Interceptor Interfaces

• IDbCommandInterceptor: Handles methods related to Commands; this article will use this interface.
• IDbConnectionInterceptor: Handles methods related to opening and closing connections.
• IDbTransactionInterceptor: Handles methods related to transactions.
• ISaveChangesInterceptor: Handles methods related to SaveChanges().

Implementation Method

Regarding the handling of WITH (NOLOCK), most solutions on the internet cannot handle subqueries. However, there is an article "Adding With NoLock to EF Core Queries" that takes it a step further, so I referred to it for my modifications.

public class FixDbCommandInterceptor : DbCommandInterceptor {
    private static readonly RegexOptions regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase;
    private static readonly Regex cudRegex = new(@"\b(INSERT|UPDATE|DELETE)\b", regexOptions);
    private static readonly Regex tableAliasRegex = new(
        @"(?<tableAlias>(FROM|JOIN)\s+\[\w+\]\s+AS\s+\[\w+\])",
        regexOptions
    );

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result) {
        FixCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default
    ) {
        FixCommand(command);
        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<object> ScalarExecuting(
       DbCommand command, CommandEventData eventData,
       InterceptionResult<object> result
    ) {
        FixCommand(command);
        return base.ScalarExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<object> result, CancellationToken cancellationToken = default
    ) {
        FixCommand(command);
        return await base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }

    private static void FixCommand(IDbCommand command) {
        string commandText = command.CommandText;

        // For some modification scenarios, such as querying before modifying, EF might call ExecuteReader instead of ExecuteNonQuery
        // So we need to exclude this case
        if (cudRegex.IsMatch(commandText)) {
            return;
        }

        // If Single or First is called, it might be for precise data retrieval (e.g., fetching data to modify), so NOLOCK should not be added
        if (!commandText.Contains("TOP(1)") && !commandText.Contains("TOP(2)")) {
            commandText = tableAliasRegex.Replace(commandText, "${tableAlias} WITH (NOLOCK)");
        }

        // Although EF-generated Select statements do not end with a semicolon, modification statements do. 
        // To be safe, we still need to handle it.
        commandText = commandText.TrimEnd(';') + " OPTION (OPTIMIZE FOR UNKNOWN);";

        command.CommandText = commandText;
    }
}

Code Explanation

1. DbCommandInterceptor has already implemented all methods of IDbCommandInterceptor, so you only need to inherit from it and override the required methods.
2. Methods related to queries include ExecuteReader() and ExecuteScalar(), so we override the pre-execution methods corresponding to IDbCommandInterceptor for both synchronous and asynchronous versions of these two methods.
3. CommandText correction:
   • Some modification syntax containing return values might use ExecuteScalar(), so we do not process syntax containing INSERT, UPDATE, and DELETE.
   • WITH (NOLOCK) is intended to avoid blocking when data is locked, but it is not suitable if you are fetching data to perform modifications. Therefore, we do not process syntax containing TOP(1) (e.g., First() or Find()) and TOP(2) (e.g., Single()).
   • Added OPTION (OPTIMIZE FOR UNKNOWN); to handle Parameter Sniffing.

WARNING

The above handling lacks verification in actual use; please adjust it according to your own situation.

Adding the Interceptor

You can add the Interceptor using the following two methods:

• Add the following code in DbContext:

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.AddInterceptors(new FixDbCommandInterceptor());

• When injecting DbContext in DI, configure it from DbContextOptionsBuilder:

services.AddDbContext<TestDbContext>(options => {
    options
        .UseSqlServer(DbConnectionString)
        .AddInterceptors(new FixDbCommandInterceptor());
});

Actual Execution Results

Use the following SQL to create the table and use reverse engineering to build the EF:

CREATE TABLE [dbo].[Test](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestInt] [int] NOT NULL,
    [TestBit] [bit] NOT NULL,
    [TestDateTime] [datetime2](7) NOT NULL,
    [TestGuid] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED (
 [Id] ASC
) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SubTest](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestId] [int] NOT NULL,
    CONSTRAINT [PK_SubTest] PRIMARY KEY CLUSTERED (
 [Id] ASC
) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SubTest]  WITH CHECK ADD  CONSTRAINT [FK_SubTest_Test] FOREIGN KEY([TestId])
REFERENCES [dbo].[Test] ([Id])
GO

Execute the following program:

context.Tests.Find(1);

context.Tests
    .Include(x => x.SubTests)
    .SingleOrDefault(x => x.Id == 1);

context.Tests
    .Include(x => x.SubTests)
    .ToList();

The generated SQL syntax is as follows:

-- Find()
SELECT TOP(1) [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt]
FROM [Test] AS [t]
WHERE [t].[Id] = @__p_0 OPTION (OPTIMIZE FOR UNKNOWN);

-- SingleOrDefault()
SELECT [t0].[Id], [t0].[TestBit], [t0].[TestDateTime], [t0].[TestGuid], [t0].[TestInt], [s].[Id], [s].[TestId]
FROM (
    SELECT TOP(2) [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt]
    FROM [Test] AS [t]
    WHERE [t].[Id] = 1
) AS [t0]
LEFT JOIN [SubTest] AS [s] ON [t0].[Id] = [s].[TestId]
ORDER BY [t0].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

-- ToList()
SELECT [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt], [s].[Id], [s].[TestId]
FROM [Test] AS [t] WITH (NOLOCK)
LEFT JOIN [SubTest] AS [s] WITH (NOLOCK) ON [t].[Id] = [s].[TestId]
ORDER BY [t].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

Revision History

• 2024-07-18 Initial document creation.